from flask import Blueprint, render_template, request, redirect, url_for, flash, make_response, session, current_app
from function.sqlite_function import *
from time import localtime

_export = Blueprint('export', __name__, url_prefix='/')


# 返回页面
@_export.route('/export')
def export1():
    # 大类预设数据
    temp1 = return_bigname()
    return render_template('export/export.html', data=temp1)


# 选定大类后返回小类
@_export.route('/export2', methods=['POST'])
def export2():
    bigname = request.form.get('bigname').strip()
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select smallname 
        from stateless_part
        where bigname = '{bigname}'
    ''')
    return render_template('export/export_box32.html', data=temp1)


# 返回大类
@_export.route('/export3', methods=['POST'])
def export3():
    temp1 = return_bigname()
    return render_template('export/export_box31.html', data=temp1)


# 返回选定日期内的出库表
# 时间,领用单位，小类名称，数量，单价，金额
@_export.route('/export4', methods=['GET'])
def export4():
    start = request.args.get('a')
    stop = request.args.get('b')
    con, cur = db_open()

    # 检查目前有多少张大类表
    temp1 = return_bigname()

    cmy = []

    for i in temp1:
        temp2 = sqliteObject_to_list_h(cur, f'''
            select date, handle, bigname, smallname, change, unitprice, round(price,2)
            from '{i}'
            where DATE(date) >= '{start}' and DATE(date) <= '{stop}' and change < 0
        ''')
        for j in temp2:
            cmy.append([j[0], j[1], j[2], j[3], abs(j[4]), j[5], abs(j[6])])
    # 计算本页面总价格
    total = 0
    for i in range(len(cmy)):
        total = total + cmy[i][6]
    cmy.append(['==========', '', '', '', '', '', ''])
    cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])

    return render_template('export/export_box33.html', data=cmy)


# 返回选定日期内的入库表
@_export.route('/export5', methods=['GET'])
def export5():
    start = request.args.get('a')
    stop = request.args.get('b')
    con, cur = db_open()

    # 检查目前有多少张大类表
    temp1 = return_bigname()
    cmy = []

    for i in temp1:
        temp2 = sqliteObject_to_list_h(cur, f'''
            select date, handle, bigname, smallname, change, unitprice, round(price,2)
            from '{i}'
            where DATE(date) >= '{start}' and DATE(date) <= '{stop}' and change > 0
        ''')
        for j in temp2:
            cmy.append([j[0], j[1], j[2], j[3], j[4], j[5], j[6]])
    # 计算本页面总价格
    total = 0
    for i in range(len(cmy)):
        total = total + cmy[i][6]
    cmy.append(['==========', '', '', '', '', '', ''])
    cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
    return render_template('export/export_box33.html', data=cmy)


# 返回选定日期内的轴承表
@_export.route('/export6', methods=['GET'])
def export6():
    start = request.args.get('a')
    stop = request.args.get('b')
    con, cur = db_open()

    cmy = []
    temp1 = return_bigname()
    if '轴承' not in temp1:
        return '请先创建名称为“轴承”大类库'

    temp2 = sqliteObject_to_list_h(cur, f'''
            select date, handle, bigname, smallname, change, unitprice, round(price,2)
            from '轴承'
            where DATE(date) >= '{start}' and DATE(date) <= '{stop}' and change < 0
        ''')
    for j in temp2:
        cmy.append([j[0], j[1], j[2], j[3], abs(j[4]), j[5], abs(j[6])])
    # 计算本页面总价格
    total = 0
    for i in range(len(cmy)):
        total = total + cmy[i][6]
    cmy.append(['==========', '', '', '', '', '', ''])
    cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
    return render_template('export/export_box33.html', data=cmy)


# 返回选定日期内的磁棒表
@_export.route('/export7', methods=['GET'])
def export7():
    start = request.args.get('a')
    stop = request.args.get('b')
    con, cur = db_open()

    cmy = []
    temp1 = return_bigname()
    if '磁棒-锯片' not in temp1:
        return '请先创建名称为“磁棒-锯片”大类库'

    temp2 = sqliteObject_to_list_h(cur, f'''
            select date, handle, bigname, smallname, change, unitprice, round(price,2)
            from '磁棒-锯片'
            where DATE(date) >= '{start}' and DATE(date) <= '{stop}' and change < 0 and smallname like '%磁棒%'
        ''')
    for j in temp2:
        cmy.append([j[0], j[1], j[2], j[3], abs(j[4]), j[5], abs(j[6])])
    # 计算本页面总价格
    total = 0
    for i in range(len(cmy)):
        total = total + cmy[i][6]
    cmy.append(['==========', '', '', '', '', '', ''])
    cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
    return render_template('export/export_box33.html', data=cmy)


# 返回选定日期内的锯片表
@_export.route('/export10', methods=['GET'])
def export10():
    start = request.args.get('a')
    stop = request.args.get('b')
    con, cur = db_open()

    cmy = []
    temp1 = return_bigname()
    if '磁棒-锯片' not in temp1:
        return '请先创建名称为“磁棒-锯片”大类库'

    temp2 = sqliteObject_to_list_h(cur, f'''
                select date, handle, bigname, smallname, change, unitprice, round(price,2)
                from '磁棒-锯片'
                where DATE(date) >= '{start}' and DATE(date) <= '{stop}' and change < 0 and smallname like '%锯片%'
            ''')
    for j in temp2:
        cmy.append([j[0], j[1], j[2], j[3], abs(j[4]), j[5], abs(j[6])])
    # 计算本页面总价格
    total = 0
    for i in range(len(cmy)):
        total = total + cmy[i][6]
    cmy.append(['==========', '', '', '', '', '', ''])
    cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
    return render_template('export/export_box33.html', data=cmy)


# 返回选定日期内的大额支出表（超过1000）
@_export.route('/export8', methods=['GET'])
def export8():
    start = request.args.get('a')
    stop = request.args.get('b')
    con, cur = db_open()

    # 检查目前有多少张大类表
    temp1 = return_bigname()

    cmy = []

    for i in temp1:
        temp2 = sqliteObject_to_list_h(cur, f'''
            select date, handle, bigname, smallname, change, unitprice, round(price,2)
            from '{i}'
            where DATE(date) >= '{start}' and DATE(date) <= '{stop}' and change < 0 and price <= -1000
        ''')
        for j in temp2:
            cmy.append([j[0], j[1], j[2], j[3], abs(j[4]), j[5], abs(j[6])])
    # 计算本页面总价格
    total = 0
    for i in range(len(cmy)):
        total = total + cmy[i][6]
    cmy.append(['==========', '', '', '', '', '', ''])
    cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
    return render_template('export/export_box33.html', data=cmy)


# 选定日期和大类小类，输入记录
@_export.route('/export9', methods=['GET'])
def export9():
    start = request.args.get('a')
    stop = request.args.get('b')
    bigname = request.args.get('c')
    smallname = request.args.get('d')

    con, cur = db_open()

    # 返回全部数据
    if bigname == '' and smallname == '':
        cmy = []
        temp1 = return_bigname()
        for i in temp1:
            temp2 = sqliteObject_to_list_h(cur, f'''
                select date, handle, bigname, smallname, change, unitprice, round(price,2)
                from '{i}'
                where DATE(date) >= '{start}' and DATE(date) <= '{stop}'
            ''')
            for j in temp2:
                cmy.append([j[0], j[1], j[2], j[3], j[4], j[5], j[6]])
        # 计算本页面总价格
        total = 0
        for i in range(len(cmy)):
            total = total + cmy[i][6]
        cmy.append(['==========', '', '', '', '', '', ''])
        cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
        return render_template('export/export_box33.html', data=cmy)

    # 返回某大类的数据
    if bigname != '' and smallname == '':
        cmy = []
        temp3 = sqliteObject_to_list_h(cur, f'''
            select date, handle, bigname, smallname, change, unitprice, round(price,2)
            from '{bigname}'
            where DATE(date) >= '{start}' and DATE(date) <= '{stop}'
        ''')
        for j in temp3:
            cmy.append([j[0], j[1], j[2], j[3], j[4], j[5], j[6]])
        # 计算本页面总价格
        total = 0
        for i in range(len(cmy)):
            total = total + cmy[i][6]
        cmy.append(['==========', '', '', '', '', '', ''])
        cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
        return render_template('export/export_box33.html', data=cmy)

    if bigname != '' and smallname != '':
        cmy = []
        temp4 = sqliteObject_to_list_h(cur, f'''
            select date, handle, bigname, smallname, change, unitprice, round(price,2)
            from '{bigname}'
            where DATE(date) >= '{start}' and DATE(date) <= '{stop}' and smallname = '{smallname}'
        ''')
        for j in temp4:
            cmy.append([j[0], j[1], j[2], j[3], j[4], j[5], j[6]])
        # 计算本页面总价格
        total = 0
        for i in range(len(cmy)):
            total = total + cmy[i][6]
        cmy.append(['==========', '', '', '', '', '', ''])
        cmy.append(['本账单零件总价格', round(total, 2), '', '', '', '', ''])
        return render_template('export/export_box33.html', data=cmy)


# 以一维数组形式返回大类名称
def return_bigname():
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select name from bigname
    ''')
    return temp1
